﻿using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using WTAppletProjectHTTPS.Models;

namespace WTAppletProjectHTTPS.Controllers
{

    public class ColaFirstPiecePatrolInspectionRecordQueryController : ApiController
    {
        private static readonly Logger Logger = NLog.LogManager.GetCurrentClassLogger();

        // POST api/<controller>
        public async Task<HttpResponseMessage> Post([FromBody] ColaFirstPiecePatrolInspectionRecordQuery CFPIRQ)
        {
            ReturnObjectQuery RO = new ReturnObjectQuery();
            if (CFPIRQ.PageNumber <= 0 || CFPIRQ.PageSize <= 0)
            {

                RO.Status = HttpStatusCode.BadRequest;
                RO.Message = "分页参数无效";
                RO.Data = null;
                Logger.Error("分页参数无效");
                return Request.CreateResponse(HttpStatusCode.BadRequest, RO);
            }

            try
            {
                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
                {
                    await connection.OpenAsync();

                    var whereClause = "WHERE 1=1";
                    if (CFPIRQ.CFPIR_ID != 0)
                    {
                        whereClause += " AND CFPID = @ID";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_Inspector))
                    {
                        whereClause += " AND CFPInspector LIKE @Inspector";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_Shift))
                    {
                        whereClause += " AND CFPShift LIKE @Shift";
                    }
                    if (CFPIRQ.CFPIR_InspectionTime.HasValue)
                    {
                        whereClause += " AND CFPInspectionTime = @InspectionTime";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_LineParting))
                    {
                        whereClause += " AND CFPLineParting LIKE @LineParting";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_InspectionTiming))
                    {
                        whereClause += " AND CFPInspectionTiming LIKE @InspectionTiming";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductModel))
                    {
                        whereClause += " AND CFPProductModel LIKE @ProductModel";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_OrderNumber))
                    {
                        whereClause += " AND CFPOrderNumber LIKE @OrderNumber";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_InspectionItem))
                    {
                        whereClause += " AND CFPInspectionItem LIKE @InspectionItem";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductMarking))
                    {
                        whereClause += " AND CFPProductMarking LIKE @ProductMarking";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductAppearance))
                    {
                        whereClause += " AND CFPProductAppearance LIKE @ProductAppearance";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductAssemblyInspection))
                    {
                        whereClause += " AND CFPProductAssemblyInspection LIKE @ProductAssemblyInspection";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProcessPersonnelQualification))
                    {
                        whereClause += " AND CFPProcessPersonnelQualification LIKE @ProcessPersonnelQualification";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProcessEquipmentTooling))
                    {
                        whereClause += " AND CFPProcessEquipmentTooling LIKE @ProcessEquipmentTooling";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProcessMaterialConfirmation))
                    {
                        whereClause += " AND CFPProcessMaterialConfirmation LIKE @ProcessMaterialConfirmation";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProcessMethodConfirmation))
                    {
                        whereClause += " AND CFPProcessMethodConfirmation LIKE @ProcessMethodConfirmation";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductPerformance))
                    {
                        whereClause += " AND CFPProductPerformance LIKE @ProductPerformance";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductContinuityTest))
                    {
                        whereClause += " AND CFPProductContinuityTest LIKE @ProductContinuityTest";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductHandfeel))
                    {
                        whereClause += " AND CFPProductHandfeel LIKE @ProductHandfeel";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductPackagingInspection))
                    {
                        whereClause += " AND CFPProductPackagingInspection LIKE @ProductPackagingInspection";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductBarcodeScan))
                    {
                        whereClause += " AND CFPProductBarcodeScan LIKE @ProductBarcodeScan";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_FinalConclusion))
                    {
                        whereClause += " AND CFPFinalConclusion LIKE @FinalConclusion";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_WhichInspectionQualified))
                    {
                        whereClause += " AND CFPWhichInspectionQualified LIKE @WhichInspectionQualified";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceDescription1))
                    {
                        whereClause += " AND CFPNonconformanceDescription1 LIKE @NonconformanceDescription1";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceImage1))
                    {
                        whereClause += " AND CFPNonconformanceImage1 LIKE @NonconformanceImage1";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceDescription2))
                    {
                        whereClause += " AND CFPNonconformanceDescription2 LIKE @NonconformanceDescription2";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceImage2))
                    {
                        whereClause += " AND CFPNonconformanceImage2 LIKE @NonconformanceImage2";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceDescription3))
                    {
                        whereClause += " AND CFPNonconformanceDescription3 LIKE @NonconformanceDescription3";
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceImage3))
                    {
                        whereClause += " AND CFPNonconformanceImage3 LIKE @NonconformanceImage3";
                    }

                    var query = $@"
                        SELECT 
                            CFPID, CFPInspector, CFPShift, CFPInspectionTime, CFPLineParting, 
                            CFPInspectionTiming, CFPProductModel, CFPOrderNumber, CFPInspectionItem, 
                            CFPProductMarking, CFPProductAppearance, CFPProductAssemblyInspection, 
                            CFPProcessPersonnelQualification, CFPProcessEquipmentTooling, CFPProcessMaterialConfirmation, 
                            CFPProcessMethodConfirmation, CFPProductPerformance, CFPProductContinuityTest, 
                            CFPProductHandfeel, CFPProductPackagingInspection, CFPProductBarcodeScan, 
                            CFPFinalConclusion, CFPWhichInspectionQualified, CFPNonconformanceDescription1, 
                            CFPNonconformanceImage1, CFPNonconformanceDescription2, CFPNonconformanceImage2, 
                            CFPNonconformanceDescription3, CFPNonconformanceImage3,CFPNonconformanceDescription4, CFPNonconformanceImage4,CFPCreateCreator,CFPCreateTime, COUNT(*) OVER() AS TotalCount
                        FROM 
                            (SELECT 
                                CFPID, CFPInspector, CFPShift, CFPInspectionTime, CFPLineParting, 
                                CFPInspectionTiming, CFPProductModel, CFPOrderNumber, CFPInspectionItem, 
                                CFPProductMarking, CFPProductAppearance, CFPProductAssemblyInspection, 
                                CFPProcessPersonnelQualification, CFPProcessEquipmentTooling, CFPProcessMaterialConfirmation, 
                                CFPProcessMethodConfirmation, CFPProductPerformance, CFPProductContinuityTest, 
                                CFPProductHandfeel, CFPProductPackagingInspection, CFPProductBarcodeScan, 
                                CFPFinalConclusion, CFPWhichInspectionQualified, CFPNonconformanceDescription1, 
                                CFPNonconformanceImage1, CFPNonconformanceDescription2, CFPNonconformanceImage2, 
                                CFPNonconformanceDescription3, CFPNonconformanceImage3,CFPNonconformanceDescription4, CFPNonconformanceImage4,CFPCreateCreator,CFPCreateTime, ROW_NUMBER() OVER(ORDER BY CFPID) AS RowNum
                             FROM ColaFirstPiecePatrolInspectionRecord {whereClause}) AS Result
                        WHERE 
                            RowNum BETWEEN @StartRow AND @EndRow
                        ORDER BY RowNum";

                    int startRow = (CFPIRQ.PageNumber - 1) * CFPIRQ.PageSize + 1;
                    int endRow = CFPIRQ.PageNumber * CFPIRQ.PageSize;

                    var parameters = new List<SqlParameter>
                    {
                        new SqlParameter("@StartRow", SqlDbType.Int) { Value = startRow },
                        new SqlParameter("@EndRow", SqlDbType.Int) { Value = endRow }
                    };
                    if (CFPIRQ.CFPIR_ID != 0)
                    {
                        parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = CFPIRQ.CFPIR_ID });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_Inspector))
                    {
                        parameters.Add(new SqlParameter("@Inspector", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_Inspector}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_Shift))
                    {
                        parameters.Add(new SqlParameter("@Shift", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_Shift}%" });
                    }
                    if (CFPIRQ.CFPIR_InspectionTime.HasValue)
                    {
                        parameters.Add(new SqlParameter("@InspectionTime", SqlDbType.DateTime) { Value = CFPIRQ.CFPIR_InspectionTime });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_LineParting))
                    {
                        parameters.Add(new SqlParameter("@LineParting", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_LineParting}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_InspectionTiming))
                    {
                        parameters.Add(new SqlParameter("@InspectionTiming", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_InspectionTiming}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductModel))
                    {
                        parameters.Add(new SqlParameter("@ProductModel", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductModel}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_OrderNumber))
                    {
                        parameters.Add(new SqlParameter("@OrderNumber", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_OrderNumber}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_InspectionItem))
                    {
                        parameters.Add(new SqlParameter("@InspectionItem", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_InspectionItem}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductMarking))
                    {
                        parameters.Add(new SqlParameter("@ProductMarking", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductMarking}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductAppearance))
                    {
                        parameters.Add(new SqlParameter("@ProductAppearance", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductAppearance}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductAssemblyInspection))
                    {
                        parameters.Add(new SqlParameter("@ProductAssemblyInspection", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductAssemblyInspection}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProcessPersonnelQualification))
                    {
                        parameters.Add(new SqlParameter("@ProcessPersonnelQualification", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProcessPersonnelQualification}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProcessEquipmentTooling))
                    {
                        parameters.Add(new SqlParameter("@ProcessEquipmentTooling", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProcessEquipmentTooling}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProcessMaterialConfirmation))
                    {
                        parameters.Add(new SqlParameter("@ProcessMaterialConfirmation", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProcessMaterialConfirmation}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProcessMethodConfirmation))
                    {
                        parameters.Add(new SqlParameter("@ProcessMethodConfirmation", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProcessMethodConfirmation}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductPerformance))
                    {
                        parameters.Add(new SqlParameter("@ProductPerformance", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductPerformance}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductContinuityTest))
                    {
                        parameters.Add(new SqlParameter("@ProductContinuityTest", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductContinuityTest}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductHandfeel))
                    {
                        parameters.Add(new SqlParameter("@ProductHandfeel", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductHandfeel}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductPackagingInspection))
                    {
                        parameters.Add(new SqlParameter("@ProductPackagingInspection", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductPackagingInspection}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_ProductBarcodeScan))
                    {
                        parameters.Add(new SqlParameter("@ProductBarcodeScan", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_ProductBarcodeScan}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_FinalConclusion))
                    {
                        parameters.Add(new SqlParameter("@FinalConclusion", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_FinalConclusion}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_WhichInspectionQualified))
                    {
                        parameters.Add(new SqlParameter("@WhichInspectionQualified", SqlDbType.NVarChar, 50) { Value = $"%{CFPIRQ.CFPIR_WhichInspectionQualified}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceDescription1))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceDescription1", SqlDbType.NVarChar, 100) { Value = $"%{CFPIRQ.CFPIR_NonconformanceDescription1}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceImage1))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceImage1", SqlDbType.NVarChar, 100) { Value = $"%{CFPIRQ.CFPIR_NonconformanceImage1}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceDescription2))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceDescription2", SqlDbType.NVarChar, 100) { Value = $"%{CFPIRQ.CFPIR_NonconformanceDescription2}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceImage2))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceImage2", SqlDbType.NVarChar, 100) { Value = $"%{CFPIRQ.CFPIR_NonconformanceImage2}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceDescription3))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceDescription3", SqlDbType.NVarChar, 100) { Value = $"%{CFPIRQ.CFPIR_NonconformanceDescription3}%" });
                    }
                    if (!string.IsNullOrEmpty(CFPIRQ.CFPIR_NonconformanceImage3))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceImage3", SqlDbType.NVarChar, 100) { Value = $"%{CFPIRQ.CFPIR_NonconformanceImage3}%" });
                    }

                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (!reader.HasRows)
                            {
                                Logger.Warn("未找到记录");
                                RO.Status = HttpStatusCode.NotFound;
                                RO.Message = "未找到记录";
                                RO.Data = null;
                                return Request.CreateResponse(HttpStatusCode.NotFound, RO);
                            }

                            var records = new List<ColaFirstPiecePatrolInspectionRecord>();
                            int totalCount = 0;
                            while (await reader.ReadAsync())
                            {
                                if (totalCount == 0)
                                {
                                    totalCount = reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }

                                records.Add(new ColaFirstPiecePatrolInspectionRecord
                                {
                                    CFPIR_ID = reader.GetInt32(reader.GetOrdinal("CFPID")),
                                    CFPIR_Inspector = reader.GetString(reader.GetOrdinal("CFPInspector")),
                                    CFPIR_Shift = reader.GetString(reader.GetOrdinal("CFPShift")),
                                    CFPIR_InspectionTime = reader.GetDateTime(reader.GetOrdinal("CFPInspectionTime")),
                                    CFPIR_LineParting = reader.GetString(reader.GetOrdinal("CFPLineParting")),
                                    CFPIR_InspectionTiming = reader.GetString(reader.GetOrdinal("CFPInspectionTiming")),
                                    CFPIR_ProductModel = reader.GetString(reader.GetOrdinal("CFPProductModel")),
                                    CFPIR_OrderNumber = reader.GetString(reader.GetOrdinal("CFPOrderNumber")),
                                    CFPIR_InspectionItem = reader.GetString(reader.GetOrdinal("CFPInspectionItem")),
                                    CFPIR_ProductMarking = reader.GetString(reader.GetOrdinal("CFPProductMarking")),
                                    CFPIR_ProductAppearance = reader.GetString(reader.GetOrdinal("CFPProductAppearance")),
                                    CFPIR_ProductAssemblyInspection = reader.GetString(reader.GetOrdinal("CFPProductAssemblyInspection")),
                                    CFPIR_ProcessPersonnelQualification = reader.GetString(reader.GetOrdinal("CFPProcessPersonnelQualification")),
                                    CFPIR_ProcessEquipmentTooling = reader.GetString(reader.GetOrdinal("CFPProcessEquipmentTooling")),
                                    CFPIR_ProcessMaterialConfirmation = reader.GetString(reader.GetOrdinal("CFPProcessMaterialConfirmation")),
                                    CFPIR_ProcessMethodConfirmation = reader.GetString(reader.GetOrdinal("CFPProcessMethodConfirmation")),
                                    CFPIR_ProductPerformance = reader.GetString(reader.GetOrdinal("CFPProductPerformance")),
                                    CFPIR_ProductContinuityTest = reader.GetString(reader.GetOrdinal("CFPProductContinuityTest")),
                                    CFPIR_ProductHandfeel = reader.GetString(reader.GetOrdinal("CFPProductHandfeel")),
                                    CFPIR_ProductPackagingInspection = reader.GetString(reader.GetOrdinal("CFPProductPackagingInspection")),
                                    CFPIR_ProductBarcodeScan = reader.GetString(reader.GetOrdinal("CFPProductBarcodeScan")),
                                    CFPIR_FinalConclusion = reader.GetString(reader.GetOrdinal("CFPFinalConclusion")),
                                    CFPIR_WhichInspectionQualified = reader.GetString(reader.GetOrdinal("CFPWhichInspectionQualified")),
                                    CFPIR_NonconformanceDescription1 = reader.GetString(reader.GetOrdinal("CFPNonconformanceDescription1")),
                                    CFPIR_NonconformanceImage1 = reader.GetString(reader.GetOrdinal("CFPNonconformanceImage1")),
                                    CFPIR_NonconformanceDescription2 = reader.GetString(reader.GetOrdinal("CFPNonconformanceDescription2")),
                                    CFPIR_NonconformanceImage2 = reader.GetString(reader.GetOrdinal("CFPNonconformanceImage2")),
                                    CFPIR_NonconformanceDescription3 = reader.GetString(reader.GetOrdinal("CFPNonconformanceDescription3")),
                                    CFPIR_NonconformanceImage3 = reader.GetString(reader.GetOrdinal("CFPNonconformanceImage3")),
                                    CFPIR_NonconformanceDescription4 = reader.GetString(reader.GetOrdinal("CFPNonconformanceDescription4")),
                                    CFPIR_NonconformanceImage4 = reader.GetString(reader.GetOrdinal("CFPNonconformanceImage4")),
                                    CFPIR_CreateCreator = reader.GetString(reader.GetOrdinal("CFPCreateCreator")),
                                    CFPIR_CreateTime = reader.GetDateTime(reader.GetOrdinal("CFPCreateTime"))
                                });
                            }

                            Logger.Info("查询成功");
                            RO.Status = HttpStatusCode.OK;
                            RO.Message = "OK";
                            RO.Data = records;
                            RO.TotalCount = totalCount;
                            RO.PageNumber = CFPIRQ.PageNumber;
                            RO.PageSize = CFPIRQ.PageSize;
                            return Request.CreateResponse(HttpStatusCode.OK, RO);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error("查询失败: " + ex.Message);
                RO.Status = HttpStatusCode.InternalServerError;
                RO.Message = "Error: " + ex.Message;
                RO.Data = null;
                return Request.CreateResponse(HttpStatusCode.InternalServerError, RO);
            }
        }
    }
}
